package com.steellee.leeexcel.service;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.data.DataFormatData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.util.BooleanUtils;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.steellee.leeexcel.abstruct.PaOwnCheckMapExtend;
import com.steellee.leeexcel.dto.*;
import com.steellee.leeexcel.exception.BusinessException;
import com.steellee.leeexcel.exception.MsgCode;
import com.steellee.leeexcel.msg.JSONMessage;
import com.steellee.leeexcel.util.CustomCellWriteHandler;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.ObjectUtils;

import java.io.InputStream;
import java.io.OutputStream;
import java.util.*;

/**
 * Excel文件解析操作方法实现类
 *
 * @author steellee
 * @date 2023-02-17
 */

public class PaExcelReadForMapStart {

    public static final String ERR_MSG = "errMsg";
    public static final String ERR_INDEX = "errIndex";
    public static final String DATA = "data";
    public static final String SHEET_INFO = "sheetInfo";
    private static final Logger logger = LoggerFactory.getLogger(PaExcelReadForMapStart.class);

    /**
     * 方案三: 入参对象集传递 InputStream文件流必填, List<MetaExcelDTO>包含方案一参数集, 可读取多个 sheet
     *
     * @param inputStream 入参文件流
     * @param param       入参MetaExceIDTO集合
     * @return
     */
    public JSONMessage readForMap(InputStream inputStream, List<MetaExcelReadDTO> param) {
        long start = System.currentTimeMillis();
        Map<String, Object> result = new HashMap<>();
        try {
            Map<String, Object> data = getMoreData(inputStream, param);

            //匹配模板是否正确
            if (!ObjectUtils.isEmpty(result.get("modelFlag")) && "trte".equals(result.get("modelFlag").toString())) {
                return JSONMessage.success(MsgCode.FAIL_TEMP_ERR.code, MsgCode.FAIL_TEMP_ERR.msg);
            }

            //匹配数据行数是否大于指定行
            if (!ObjectUtils.isEmpty(result.get("rowCheckFlag")) && "true".equals(result.get("rowCheckFlag").toString())) {
                return JSONMessage.success(MsgCode.CHECK_ROW_NUM_ERR.code, MsgCode.CHECK_ROW_NUM_ERR.msg);
            }

            //用于存放返回全量数据
            Map<String, Object> changMap = new HashMap<>();
            Map<Integer, List<Map<Object, Object>>> datas = (Map<Integer, List<Map<Object, Object>>>) data.get(DATA);

            // 遍历数据, 通过sheetNo关联存放
            for (MetaExcelReadDTO meta : param) {
                if (null != meta.getHeads() && meta.getHeads().length > 0) {
                    changMap.put(meta.getSheetNo().toString(), changeKey(meta.getHeads(), datas.get(meta.getSheetNo())));
                }
            }
            data.put("data", changMap);
            result.put("excelData", data);
            result.putAll(changMap);
        } catch (IndexOutOfBoundsException e) {
            return JSONMessage.success(MsgCode.FAIL_TEMP_ERR.code, MsgCode.FAIL_TEMP_ERR.msg);
        } catch (BusinessException e) {
            return JSONMessage.success(e.getCode(), e.getMessage());
        }
        logger.info("paExcel read use time: {} ms", System.currentTimeMillis() - start);
        return JSONMessage.success(MsgCode.FILE_ANALYSIS_SUCCESS.code, MsgCode.FILE_ANALYSIS_SUCCESS.msg, result);
    }


    /**
     * 获取单个 sheet数据
     *
     * @param inputStream  入参文件流
     * @param sheetNo      指定 sheet页
     * @param dateStartRow 指定最大行数
     * @return
     */
    private List<Map<Object, Object>> getData(InputStream inputStream, Integer sheetNo, Integer dateStartRow) {
        List<Map<Object, Object>> result = new ArrayList<>();
        try {
            EasyExcel.read(inputStream, new AnalysisEventListener<Map<Object, Object>>() {

                @Override
                public void invoke(Map<Object, Object> integerObjectMap, AnalysisContext analysisContext) {
                    result.add(integerObjectMap);
                }

                @Override
                public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                }
            }).sheet(sheetNo).headRowNumber(dateStartRow).doRead();
        } catch (Exception e) {
            throw new BusinessException(MsgCode.FILE_ANALYSIS_ERR.code, MsgCode.FILE_ANALYSIS_ERR.msg);
        }
        return result;
    }

    /**
     * 获取多个 sheet数据
     *
     * @param inputstream 入参文件流
     * @param param       入参MetaExcelDTO集合
     * @return
     **/
    private Map<String, Object> getMoreData(InputStream inputstream, List<MetaExcelReadDTO> param) {
        Map<String, Object> resultMap = new HashMap<>();
        Map<Integer, List<Map<Object, Object>>> result = new HashMap<>();
        List<MetaSheetInfo> metaSheets = new ArrayList<>();
        try {
            ExcelReader excelReader = EasyExcel.read(inputstream).build();
            if (!ObjectUtils.isEmpty(param)) {
                List<ReadSheet> readSheets = new ArrayList<>();

                for (MetaExcelReadDTO dto : param) {
                    // 存放单个 sheet数据
                    List<Map<Object, Object>> sheetData = new ArrayList<>();
                    Integer sheetNo = getSheet(dto);
                    Integer dateStartRow = getDateStartRow(dto);
                    Integer columnIndex = dto.getHeads().length;
                    ReadSheet readSheet = EasyExcel.readSheet(sheetNo).headRowNumber(dateStartRow).registerReadListener(new AnalysisEventListener<Map<Object, Object>>() {
                        @Override
                        public void invoke(Map<Object, Object> integer0bjectMap, AnalysisContext analysisContext) {
                            sheetData.add(integer0bjectMap);
                        }

                        @Override
                        public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                            String sheetName = analysisContext.readSheetHolder().getSheetName();
                            MetaSheetInfo metaSheetInfo = new MetaSheetInfo();
                            metaSheetInfo.setSheetNo(sheetNo);
                            metaSheetInfo.setSheetName(sheetName);
                            metaSheets.add(metaSheetInfo);
                            //判断最大行号是否大于指定行
                            if (null != dto.getDataStartRowNo() && null != dto.getAllowMaxRows() &&
                                    (dto.getDataStartRowNo() + dto.getAllowMaxRows()) < analysisContext.readRowHolder().getRowIndex()) {
                                resultMap.put("rowCheckFlag", "true");
                            }

                            //判断模板是否正确
                            if (columnIndex != analysisContext.readRowHolder().getCellMap().size()) {
                                resultMap.put("modelFlag", "true");
                            }
                            logger.info("{}readfinish!", sheetName);
                        }
                    }).build();
                    readSheets.add(readSheet);
                    result.put(sheetNo, sheetData);
                }
                excelReader.read(readSheets);
                excelReader.finish();
            }
        } catch (
                Exception e) {
            throw new BusinessException(MsgCode.FILE_ANALYSIS_ERR.code, MsgCode.FILE_ANALYSIS_ERR.msg);
        }

        //存放读取到的数据
        resultMap.put(SHEET_INFO, metaSheets);
        resultMap.put(DATA, result);
        return resultMap;
    }


    /**
     * 获取数据起始行
     *
     * @param param 入参MetaExcelDTO
     * @return
     */
    private Integer getDateStartRow(MetaExcelReadDTO param) {
        if (null != param.getDataStartRowNo()) {
            return param.getDataStartRowNo();
        } else {
            return 1;
        }
    }

    /**
     * 获取 sheet指定页
     *
     * @param param 入参MetaExcelDTO
     * @ return
     */
    private Integer getSheet(MetaExcelReadDTO param) {
        if (null != param.getSheetNo()) {
            return param.getSheetNo();
        } else {
            return 0;
        }
    }

    /**
     * 获取 map映射字段名
     *
     * @param heads
     * @param param
     * @return
     */
    private List<Map<Object, Object>> changeKey(String[] heads, List<Map<Object, Object>> param) {
        List<String> headKeys = Arrays.asList(heads);
        List<Map<Object, Object>> result = new ArrayList<>();
        for (Map<Object, Object> para : param) {
            Map<Object, Object> data = new HashMap<>();
            for (int i = 0; i < headKeys.size(); i++) {
                data.put(headKeys.get(i), para.get(i));
            }
            result.add(data);
        }
        return result;
    }


    /**
     * 个性化校验报红场景 --封装报红后文件
     *
     * @param param           读取文件时的入参
     * @param data            读取到的全量数据
     * @param outputstream    返回文件流
     * @param tempInputStream 写文件使用模板
     * @return
     */
    public JSONMessage checkAndReturn(List<MetaExcelReadDTO> param, Map<String, Object> data, OutputStream outputstream, InputStream tempInputStream) {
        long start = System.currentTimeMillis();

        //获取数据集
        Map<String, Object> paramData = (Map<String, Object>) data.get("excelData");
        Map<Integer, List<Map<Object, Object>>> excelData = (Map<Integer, List<Map<Object, Object>>>) paramData.get(DATA);

        // 获取 sheet + 报红单元格信息
        List<MetaSheetInfo> metaSheets = (List<MetaSheetInfo>) paramData.get(SHEET_INFO);

        //获取错误信息
        String errMsg = "";
        String errIndex = "";

        //获取业务方个性化校验结果
        Map<String, String> errReturn = getOwnCheckResult(errMsg, errIndex, excelData, metaSheets, param);
        if (!ObjectUtils.isEmpty(errReturn)) {
            errIndex = errReturn.get(ERR_INDEX);
            errMsg = errReturn.get(ERR_MSG);
        }
        if (!StringUtils.isEmpty(errMsg)) {
            logger.info("errspace:{}", errMsg);
            if (null != outputstream) {
                setFailCell(metaSheets, errIndex);
                if (null == tempInputStream) {

                    //不通过模板写
                    writeExcel(outputstream, metaSheets, param, excelData);
                } else {

                    //通过模板写出
                    writeExcelByTemp(metaSheets, tempInputStream, outputstream, param, excelData);
                }
            }

            //分装返回异常信息
            Map<String, String> resultMap = new HashMap<>();
            resultMap.put("errIndex", errIndex);
            resultMap.put("errMsg", errMsg);
            return JSONMessage.success(MsgCode.CHECK_NOT_NULL_ERR.code, "校验存在异常 !", resultMap);
        }
        logger.info("paExcelcheckAndReturnusetime:{}ms", System.currentTimeMillis() - start);

        //无异常返回全量数据
        return JSONMessage.success(MsgCode.FILE_ANALYSIS_SUCCESS.code, MsgCode.FILE_ANALYSIS_SUCCESS.msg, excelData);
    }


    /**
     * 不通过模板写文件
     *
     * @param outputStream 返回文件流
     * @param metaSheets   各个 sheet信息
     * @param param        读取文件入参信息
     * @param excelData    读取文件全量数据
     */
    private void writeExcel(OutputStream outputStream, List<MetaSheetInfo> metaSheets, List<MetaExcelReadDTO> param, Map<Integer, List<Map<Object, Object>>> excelData) {
        try {
            ExcelWriter excelWriter = EasyExcel.write(outputStream)
                    .registerWriteHandler(new CellWriteHandler() {
                        @Override
                        public void afterCellDispose(CellWriteHandlerContext context) {
                            //设置单元格样式
                            WriteCellData cellData = context.getFirstCellData();
                            WriteCellStyle writeCellStyle = cellData.getOrCreateStyle();
                            writeCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
                            writeCellStyle.setBorderBottom(BorderStyle.THIN);
                            writeCellStyle.setBorderLeft(BorderStyle.THIN);
                            writeCellStyle.setBorderRight(BorderStyle.THIN);
                            writeCellStyle.setBorderTop(BorderStyle.THIN);

                            //设置单元格纯文本格式
                            DataFormatData data = new DataFormatData();
                            data.setIndex((short) 49);
                            writeCellStyle.setDataFormatData(data);

                            // 通过单元格所在 sheet， row， column判断是否是异常单元格并调整背景色为红色
                            Cell cell = context.getCell();
                            Sheet sheet = cell.getSheet();
                            String sheetName = sheet.getSheetName();
                            for (MetaSheetInfo metaSheetInfo : metaSheets) {
                                if (sheetName.equals(metaSheetInfo.getSheetName())) {

                                    //获取所有异常需报红单元格
                                    List<FailCell> failCells = metaSheetInfo.getFailCells();
                                    for (FailCell failCell : failCells) {

                                        //匹配单元格行+列， 对应单元格标红
                                        if (failCell.getCellRowNo() == cell.getRowIndex() && failCell.getCellColumnNo() == cell.getColumnIndex()) {
                                            writeCellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
                                        }
                                    }
                                }
                            }

                            //设置字体
                            WriteFont writeFont = new WriteFont();
                            writeFont.setFontName("宋体");
                            if (BooleanUtils.isTrue(context.getHead())) {
                                writeFont.setBold(true);
                            }

                            writeCellStyle.setWriteFont(writeFont);
                            writeCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
                            cellData.setWriteCellStyle(writeCellStyle);
                        }
                    }).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();

            // 遍历每个 sheet入参,分别写每个 sheet数据
            for (int i = 0; i < param.size(); i++) {
                if (!ObjectUtils.isEmpty(excelData.get(param.get(i).getSheetNo().toString()))) {
                    WriteSheet writeSheet = EasyExcel.writerSheet(param.get(i).getSheetNo()).head(getWriteHeads(param.get(i).getHeads())).build();
                    excelWriter.write(getMapDataList(excelData.get(param.get(i).getSheetNo())), writeSheet);
                } else {
                    WriteSheet writeSheet = EasyExcel.writerSheet(param.get(i).getSheetNo(), "sheet"+ (i + 1)).head(getWriteHeads(param.get(i).getHeads())).build();
                    excelWriter.write(new ArrayList<>(), writeSheet);
                }
            }
            excelWriter.finish();
        } catch (Exception e) {
            throw new BusinessException(MsgCode.WRITE_STREAM_ERR.code, MsgCode.WRITE_STREAM_ERR.msg);
        }
    }

    /**
     * 拆分每个 sheet异常信息, 组装成 sheet异常类
     * 用于写出标红文件
     *
     * @param metaSheets sheet信息集合
     * @param errIndex   异常单元格下标信息(全量)
     */
    private void setFailCell(List<MetaSheetInfo> metaSheets, String errIndex) {
        String[] errMsg = errIndex.split(";");
        for (MetaSheetInfo metaSheet : metaSheets) {
            List<FailCell> failCells = new ArrayList<>();
            for (int i = 0; i < errMsg.length; i++) {

                // 匹配sheetNo相同， 对应单元格下标赋值
                if (metaSheet.getSheetNo() == Integer.valueOf(errMsg[i].split(",")[0])) {
                    FailCell failCell = new FailCell();
                    failCell.setCellRowNo(Integer.valueOf(errMsg[i].split(",")[1]));
                    failCell.setCellColumnNo(Integer.valueOf(errMsg[i].split(",")[2]));
                    failCells.add(failCell);
                }
            }
            metaSheet.setFailCells(failCells);
        }
    }


    /**
     * 将 Map数据写入指定路径文件或文件流
     *
     * @param inputStream  模板文件流
     * @param outputStream 文件流
     * @param param        入参对象集合
     **/
    private void writeExcelByTemp(List<MetaSheetInfo> metaSheets, InputStream inputStream, OutputStream outputStream, List<MetaExcelReadDTO> param, Map<Integer, List<Map<Object, Object>>> excelData) {
        ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(outputStream);
        try {
            ExcelWriter excelWriter = excelWriterBuilder.withTemplate(inputStream).build();
            for (MetaExcelReadDTO dto : param) {
                List<Map<Object, Object>> data = excelData.get(dto.getSheetNo().toString());

                // 写异常文件用到 heads
                List<List<String>> writeHeads = getWriteHeads(dto.getHeads());
                List<List<Object>> mapDataList = getMapDataList(data);
                WriteSheet writeSheet = EasyExcel.writerSheet(dto.getSheetNo()).head(writeHeads).needHead(false)
                        .registerWriteHandler(new CellWriteHandler() {
                            @Override
                            public void afterCellDispose(CellWriteHandlerContext context) {
                                //设置单元格格式
                                WriteCellData cellData = context.getFirstCellData();
                                WriteCellStyle writeCellStyle = cellData.getOrCreateStyle();
                                writeCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
                                writeCellStyle.setBorderBottom(BorderStyle.THIN);
                                writeCellStyle.setBorderLeft(BorderStyle.THIN);
                                writeCellStyle.setBorderRight(BorderStyle.THIN);
                                writeCellStyle.setBorderTop(BorderStyle.THIN);

                                //设置单元格纯文本格式
                                DataFormatData data = new DataFormatData();
                                data.setIndex((short) 49);
                                writeCellStyle.setDataFormatData(data);
                                writeCellStyle.setLocked(false);

                                // 通过单元格所在 sheet， row， column判断是否是异常单元格并调整背景色为红色
                                Cell cell = context.getCell();
                                Sheet sheet = cell.getSheet();
                                String sheetName = sheet.getSheetName();
                                for (MetaSheetInfo metaSheetInfo : metaSheets) {
                                    if (sheetName.equals(metaSheetInfo.getSheetName())) {
                                        List<FailCell> failCells = metaSheetInfo.getFailCells();
                                        for (FailCell failCell : failCells) {

                                            //匹配异常报红单元格， 并标红
                                            if (failCell.getCellRowNo() == cell.getRowIndex() && failCell.getCellColumnNo() == cell.getColumnIndex()) {
                                                writeCellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
                                            }
                                        }
                                    }
                                }

                                WriteFont writeFont = new WriteFont();
                                writeFont.setFontName("宋体");
                                if (BooleanUtils.isTrue(context.getHead())) {
                                    writeFont.setBold(true);
                                }
                                writeCellStyle.setWriteFont(writeFont);
                                writeCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
                                cellData.setWriteCellStyle(writeCellStyle);
                            }
                        }).registerWriteHandler(new CustomCellWriteHandler()).build();

                excelWriter.write(mapDataList, writeSheet);
            }
            excelWriter.finish();
        } catch (Exception e) {
            throw new BusinessException(MsgCode.WRITE_STREAM_ERR.code, MsgCode.WRITE_STREAM_ERR.msg);
        }
    }

    /**
     * map数据转换格式
     *
     * @param data 数据集合
     * @ return
     */
    private List<List<Object>> getMapDataList(List<Map<Object, Object>> data) {
        List<List<Object>> mapDataList = new ArrayList<>();
        if (!ObjectUtils.isEmpty(data)) {
            data.forEach(e -> {
                List<Object> mapData = new ArrayList<>();
                e.forEach((key, value) -> {
                    mapData.add(value);
                });
                mapDataList.add(mapData);
            });
        }
        return mapDataList;
    }


    /**
     * map- head行数据转换
     *
     * @param heads head行数组
     * @return
     */
    private List<List<String>> getWriteHeads(String[] heads) {
        List<List<String>> headsList = new ArrayList<>();
        if (null != heads && heads.length > 0) {
            List<String> headList = Arrays.asList(heads);
            headList.forEach(e -> {
                List<String> hea = new ArrayList<>();
                hea.add(e);
                headsList.add(hea);
            });
        }
        return headsList;
    }

    /**
     * 获取业务方个性化校验结果
     *
     * @param errMsg
     * @param errIndex
     * @param excelData
     * @param metaSheets
     * @param param
     */
    private Map<String, String> getOwnCheckResult(String errMsg, String errIndex, Map<Integer, List<Map<Object, Object>>> excelData, List<MetaSheetInfo> metaSheets, List<MetaExcelReadDTO> param) {
        //业务方自定义校验逻辑
        Map<String, String> errInfo = new HashMap<>();
        try {
            if (null != param.get(0).getAbstractClazz()) {
                PaOwnCheckMapExtend paOwnCheckExtend = (PaOwnCheckMapExtend) param.get(0).getAbstractClazz().newInstance();
                errInfo = paOwnCheckExtend.ownCheck(errMsg, errIndex, excelData, metaSheets, param);
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new BusinessException(MsgCode.OWN_CHECK_FAIL.code, MsgCode.OWN_CHECK_FAIL.msg);
        }
        return errInfo;
    }
}